﻿using System;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Collections.Generic;

namespace QL_Truong_Hoc.DAO
{
    public static class clsDbAccess
    {
        /// <summary>
        /// Lấy danh sách học sinh lớp nghề
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="intLopID"></param>
        /// <returns></returns>
        public static DataTable fncGetDanhSachLopNghe(Database objDb, int intLopID)
        {
            try
            {
                object[] parameters = new object[]
			    {
                    Null.GetNull(intLopID, DBNull.Value)
			    };

                using (IDataReader dataReader = objDb.ExecuteReader("usrProc_GetDanhSachLopNghe", parameters))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// Lấy danh sách lớp văn hóa
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="intLopID"></param>
        /// <returns></returns>
        public static DataTable fncGetDanhSachLopVanHoa(Database objDb, int intLopID)
        {
            try
            {
                object[] parameters = new object[]
			    {
                    Null.GetNull(intLopID, DBNull.Value)
			    };

                using (IDataReader dataReader = objDb.ExecuteReader("usrProc_GetDanhSachLopVanHoa", parameters))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// Lấy điểm môn văn hóa
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="intLopID"></param>
        /// <param name="MonID"></param>
        /// <returns></returns>
        public static DataTable fncGetDiemVanHoa(Database objDb, int intLopID, int MonID)
        {
            try
            {
                object[] parameters = new object[]
			    {
                    Null.GetNull(intLopID, DBNull.Value)
                    ,Null.GetNull(MonID, DBNull.Value)
			    };

                using (IDataReader dataReader = objDb.ExecuteReader("usrProc_GetDiemMonVanHoa", parameters))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// Lấy điểm môn nghề
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="intLopID"></param>
        /// <param name="MonID"></param>
        /// <returns></returns>
        public static DataTable fncGetDiemNghe(Database objDb, int intLopID, int MonID)
        {
            try
            {
                object[] parameters = new object[]
			    {
                    Null.GetNull(intLopID, DBNull.Value)
                    ,Null.GetNull(MonID, DBNull.Value)
			    };

                using (IDataReader dataReader = objDb.ExecuteReader("usrProc_GetDiemMonNghe", parameters))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// Lấy danh sách thi tốt nghiệp nghề
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="intTTN_ID"></param>
        /// <returns></returns>
        public static DataTable fncGetDsThiTotNghiepNghe(Database objDb, int intTTN_ID)
        {
            try
            {
                object[] parameters = new object[]
			    {
                    Null.GetNull(intTTN_ID, DBNull.Value)
			    };

                using (IDataReader dataReader = objDb.ExecuteReader("usrProc_GetDanhSachThiTotNghiep", parameters))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// Search Học sinh theo họ tên
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="strHoTen">họ tên cần search</param>
        /// <returns></returns>
        public static DataTable fncSearchHocSinh(Database objDb, string strHoTen, bool blnSearchVanHoa)
        {
            try
            {
                string strStore = "usrProc_Search_Trinhdo_Khoa_Khoas_Nghe_Lop_Hocsinh";
                if (blnSearchVanHoa) strStore = "usrProc_Search_Trinhdo_Khoa_Khoas_Nghe_LopVanHoa_Hocsinh";

                strHoTen = clsCommon.fncCnvToDbString(strHoTen).ToString();

                object[] parameters = new object[]
			    {
                    Null.GetNull(strHoTen, DBNull.Value)
			    };

                using (IDataReader dataReader = objDb.ExecuteReader(strStore, parameters))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// Search toàn bộ thông tin học tập của hs
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="strHoTen">họ tên cần search</param>
        /// <returns></returns>
        public static DataTable fncGetTrinhDo_Khoa_Khoas_Nghe_HocSinh(Database objDb, string strWhere)
        {
            try
            {
                object[] parameters = new object[]
			    {
                    Null.GetNull(strWhere, DBNull.Value)
			    };

                using (IDataReader dataReader = objDb.ExecuteReader("usrProc_Get_Trinhdo_Khoa_Khoas_Nghe_Lop_Hocsinh_WHERE", parameters))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// Lấy chi tiết các quyền trong nhóm quyền
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="intRoleID">id nhóm quyền</param>
        /// <returns></returns>
        public static DataTable fncGetNhomQuyen_Quyen(Database objDb, int intRoleID)
        {
            try
            {
                object[] parameters = new object[]
			    {
                    Null.GetNull(intRoleID, DBNull.Value)
			    };

                using (IDataReader dataReader = objDb.ExecuteReader("usrProc_Get_NhomQuyen_Quyen", parameters))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }
        
        /// <summary>
        /// Lấy điểm tổng kết sơ cấp
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="intKdtID"></param>
        /// <param name="LopID"></param>
        /// <returns></returns>
        public static DataTable fncGetTongKetSC(Database objDb, int intKdtID, int intLopID)
        {
            try
            {
                string strSQL = "";

                //lấy số môn cần học trong khung đào tạo.
                List<DTO.MKhungDaoTaoMonDTO> lstKdtMon = DAO.MKhungDaoTaoMonDAO.SelectWhere(objDb, string.Format("KDT_ID = {0}", intKdtID));
                List<DTO.MMonDTO> lstMon = new List<DTO.MMonDTO>();

                for (int i = 0; i <= lstKdtMon.Count - 1; i++)
                {
                    lstMon.Add(DAO.MMonDAO.Select(objDb, lstKdtMon[i].MO_ID));
                }

                //Build caau lệnh query;
                strSQL += " select";
                //strSQL += " tblSource.Lop_ID";
                //strSQL += ",tblSource.Lop_ten";
                strSQL += " tblSource.HS_ID";
                strSQL += ",tblSource.hs_hodem as \"Họ đệm\"";
                strSQL += ",tblSource.hs_ten as \"Tên\"";
                strSQL += ",tblSource.hs_ngaysinh as \"Ngày sinh\"";
                //strSQL += ",tblSource.Mon_ID";
                strSQL += ",tblSource.DiemKTKT as \"Điểm KTKT\"";
                strSQL += ",DiemDanhGiaTotNghiep as \"Điểm đánh giá tốt nghiệp\"";
                strSQL += ",XepLoaiTotNghiep as \"Xếp loại tốt nghiệp\"";
                strSQL += ",DiemRenLuyen as \"Điểm rèn luyện\"";
                strSQL += ",XepLoaiRenLuyen as \"Xếp loại rèn luyện\"";
                for (int i = 0; i <= lstMon.Count - 1; i++)
                {
                    //strSQL += ",Format(sum(tblSource.\"" + lstMon[i].Mo_tenmon + "\"), 9.99) as \"" + lstMon[i].Mo_tenmon + "\"";
                    strSQL += ",sum(tblSource.\"" + lstMon[i].Mo_tenmon + "\") as \"" + lstMon[i].Mo_tenmon + "\"";
                }

                strSQL += " from (";

                strSQL += " select tblTemp.Lop_ID, tblTemp.Lop_ten, tblTemp.HS_ID, tblTemp.hs_hodem, tblTemp.hs_ten, tblTemp.hs_ngaysinh, tblTemp.Mon_ID, tblTemp.DiemKTKT,";
                strSQL += " DiemDanhGiaTotNghiep, XepLoaiTotNghiep, DiemRenLuyen, XepLoaiRenLuyen";
                for (int i = 0; i <= lstMon.Count - 1; i++)
                {
                    strSQL += ",case when tblTemp.mo_tenmon=N'" + lstMon[i].Mo_tenmon + "' then tblTemp.HSM_diemtongket else 0 end as \"" + lstMon[i].Mo_tenmon + "\"";
                }

                strSQL += " from";

                strSQL += " (";
                strSQL += " SELECT   mLo.Lop_ID, mLo.Lop_ten, tLo.HS_ID, tHo.hs_hodem, tHo.hs_ten,";
                strSQL += " tHo.hs_ngaysinh, tHo3.Mon_ID,";
                strSQL += " case when tHo3.DiemLan1 > tHo3.DiemLan2 then  tHo3.DiemLan1 else tHo3.DiemLan2 end   AS DiemKTKT,";
                strSQL += " mMo.mo_tenmon, tHo1.HSM_diemtongket, mKh.HeSoMon,";
                strSQL += " mKh.TinhDiemTongKet, tHo2.DiemToanKhoa,";
                strSQL += " tHo2.DiemDanhGiaTotNghiep, tHo2.XepLoaiTotNghiep,";
                strSQL += " tHo2.DiemRenLuyen, tHo2.XepLoaiRenLuyen";
                strSQL += " FROM     mKhungDaoTaoMon mKh,";
                strSQL += " mLop mLo,";
                strSQL += " tLopHocSinh tLo,";
                strSQL += " tHocSinh tHo,";
                strSQL += " tHocSinhMon tHo1,";
                strSQL += " tHocSinhTongKet tHo2,";
                strSQL += " mMon mMo,";
                strSQL += " tHocSinhDiemTotNghiep tHo3";
                strSQL += " WHERE    mKh.KDT_ID=mLo.KDT_ID";
                strSQL += " AND      mLo.Lop_ID=tLo.Lop_ID";
                strSQL += " AND      tLo.HS_ID=tHo.HS_ID";
                strSQL += " AND      tHo.HS_ID=tHo1.HS_ID";
                strSQL += " AND      tHo.HS_ID=tHo2.HS_ID";
                strSQL += " AND      tHo1.MO_ID=mMo.MO_ID";
                strSQL += " AND      tHo.HS_ID=tHo3.HS_ID";
                strSQL += " AND      mKh.MO_ID=tHo1.MO_ID";
                strSQL += " AND      mLo.Lop_ID = " + intLopID;
                strSQL += " AND      tHo3.Mon_ID = 4";
                strSQL += " ) As tblTemp";

                strSQL += " ) As tblSource";

                strSQL += " group by Lop_ID, Lop_ten, HS_ID, hs_hodem, hs_ten, hs_ngaysinh, Mon_ID, DiemKTKT, DiemDanhGiaTotNghiep, XepLoaiTotNghiep, DiemRenLuyen, XepLoaiRenLuyen";
                strSQL += " order by Lop_ID, Lop_ten, HS_ID, hs_hodem, hs_ten, hs_ngaysinh, Mon_ID, DiemKTKT";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// Lấy điểm tổng kết trung cấp
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="intKdtID"></param>
        /// <param name="LopID"></param>
        /// <returns></returns>
        public static DataTable fncGetTongKetTC(Database objDb, int intKdtID, int intLopID)
        {
            try
            {
                string strSQL = "";

                //lấy số môn cần học trong khung đào tạo.
                List<DTO.MKhungDaoTaoMonDTO> lstKdtMon = DAO.MKhungDaoTaoMonDAO.SelectWhere(objDb, string.Format("KDT_ID = {0}", intKdtID));
                List<DTO.MMonDTO> lstMon = new List<DTO.MMonDTO>();

                for (int i = 0; i <= lstKdtMon.Count - 1; i++)
                {
                    lstMon.Add(DAO.MMonDAO.Select(objDb, lstKdtMon[i].MO_ID));
                }

                //Build caau lệnh query;
                strSQL += " select";
                //strSQL += " tblSource.Lop_ID";
                //strSQL += ",tblSource.Lop_ten";
                strSQL += " tblSource.HS_ID";
                strSQL += ",tblSource.hs_hodem as \"Họ đệm\"";
                strSQL += ",tblSource.hs_ten as \"Tên\"";
                strSQL += ",tblSource.hs_ngaysinh as \"Ngày sinh\"";

                strSQL += ",tblSource.TongKetVan as \"Tổng kết Văn\"";
                strSQL += ",tblSource.TongKetToan as \"Tổng kết Toán\"";
                strSQL += ",tblSource.TongKetLy as \"Tổng kết Lý\"";
                strSQL += ",tblSource.TongKetHoa as \"Tổng kết Hóa\"";

                strSQL += ",tblSource.TotNghiepToan as \"Thi TN Toán\"";
                strSQL += ",tblSource.TotNghiepLy as \"Thi TN Lý\"";
                strSQL += ",tblSource.TotNghiepHoa as \"Thi TN Hóa\"";

                strSQL += ",tblSource.DiemChinhTri as \"Thi Chính trị\"";
                strSQL += ",tblSource.DiemLyThuyet as \"Thi Lý thuyết\"";
                strSQL += ",tblSource.DiemThucHanh as \"Thi Thực hành\"";

                strSQL += ",tblSource.DiemToanKhoa as \"Điểm TB toàn khóa\"";
                strSQL += ",DiemDanhGiaTotNghiep as \"Điểm đánh giá tốt nghiệp\"";
                strSQL += ",XepLoaiTotNghiep as \"Xếp loại tốt nghiệp\"";
                strSQL += ",DiemRenLuyen as \"Điểm rèn luyện\"";
                strSQL += ",XepLoaiRenLuyen as \"Xếp loại rèn luyện\"";
                for (int i = 0; i <= lstMon.Count - 1; i++)
                {
                    //strSQL += ",Format(sum(tblSource.\"" + lstMon[i].Mo_tenmon + "\"), 9.99) as \"" + lstMon[i].Mo_tenmon + "\"";
                    strSQL += ",sum(tblSource.\"" + lstMon[i].Mo_tenmon + "\") as \"" + lstMon[i].Mo_tenmon + "\"";
                }

                strSQL += " from (";
                strSQL += " Select";

                strSQL += " tblTemp.HS_ID, tblTemp.hs_hodem, tblTemp.hs_ten, tblTemp.hs_ngaysinh ";
                strSQL += ",tblTemp.TongKetVan, tblTemp.TongKetToan ,tblTemp.TongKetLy, tblTemp.TongKetHoa";
                strSQL += ",tblTemp.TotNghiepToan, tblTemp.TotNghiepLy, tblTemp.TotNghiepHoa";
                strSQL += ",tblTemp.DiemChinhTri, tblTemp.DiemLyThuyet, tblTemp.DiemThucHanh";
                strSQL += ",tblTemp.DiemToanKhoa, tblTemp.DiemDanhGiaTotNghiep, tblTemp.XepLoaiTotNghiep, tblTemp.DiemRenLuyen, tblTemp.XepLoaiRenLuyen ";
                for (int i = 0; i <= lstMon.Count - 1; i++)
                {
                    strSQL += ",case when tblTemp.mo_tenmon=N'" + lstMon[i].Mo_tenmon + "' then tblTemp.HSM_diemtongket else 0 end as \"" + lstMon[i].Mo_tenmon + "\"";
                }

                strSQL += " from";

                #region TempTable
                strSQL += " (";
                strSQL += " SELECT   tHo3.HS_ID, tHo3.hs_hodem, tHo3.hs_ten, tHo3.hs_ngaysinh, ";
                strSQL += " (tVan10.diemtongket + tVan11.diemtongket + tVan12.diemtongket)/3  AS TongKetVan, ";
                strSQL += " (tToan10.diemtongket + tToan11.diemtongket + tToan12.diemtongket)/3  AS TongKetToan, ";
                strSQL += " (tLy10.diemtongket + tLy11.diemtongket + tLy12.diemtongket)/3  AS TongKetLy, ";
                strSQL += " (tHoa10.diemtongket + tHoa11.diemtongket + tHoa12.diemtongket)/3  AS TongKetHoa, ";
                strSQL += " tTnToan.tnvh_diemtotnghiep AS TotNghiepToan, ";
                strSQL += " tTnLy.tnvh_diemtotnghiep AS TotNghiepLy, ";
                strSQL += " tTnHoa.tnvh_diemtotnghiep AS TotNghiepHoa, tLo.Lop_ID, ";
                strSQL += " tHo.MO_ID, mMo.mo_tenmon, tHo.HSM_diemtongket, ";
                strSQL += " case when tChinhTri.DiemLan1 >= tChinhTri.DiemLan2 then tChinhTri.DiemLan1 else tChinhTri.DiemLan2 end as DiemChinhTri,";
                strSQL += " case when tLyThuyet.DiemLan1 >= tLyThuyet.DiemLan2 then tLyThuyet.DiemLan1 else tLyThuyet.DiemLan2 end as DiemLyThuyet,";
                strSQL += " case when tThucHanh.DiemLan1 >= tThucHanh.DiemLan2 then tThucHanh.DiemLan1 else tThucHanh.DiemLan2 end as DiemThucHanh, ";
                strSQL += " tHoTK.DiemToanKhoa, tHoTK.DiemDanhGiaTotNghiep, ";
                strSQL += " tHoTK.XepLoaiTotNghiep, tHoTK.DiemRenLuyen, ";
                strSQL += " tHoTK.XepLoaiRenLuyen ";
                strSQL += " FROM     tHocSinh tHo3, ";
                strSQL += " tHocSinhMonVanHoa tVan10, ";
                strSQL += " tHocSinhMonVanHoa tVan11, ";
                strSQL += " tHocSinhMonVanHoa tVan12, ";
                strSQL += " tHocSinhMonVanHoa tToan10, ";
                strSQL += " tHocSinhMonVanHoa tToan11, ";
                strSQL += " tHocSinhMonVanHoa tToan12, ";
                strSQL += " tHocSinhMonVanHoa tLy12, ";
                strSQL += " tHocSinhMonVanHoa tLy10, ";
                strSQL += " tHocSinhMonVanHoa tLy11, ";
                strSQL += " tHocSinhMonVanHoa tHoa10, ";
                strSQL += " tHocSinhMonVanHoa tHoa11, ";
                strSQL += " tHocSinhMonVanHoa tHoa12, ";
                strSQL += " tHocSinhDiemTotNghiepVanHoa tTnToan, ";
                strSQL += " tHocSinhDiemTotNghiepVanHoa tTnLy, ";
                strSQL += " tHocSinhDiemTotNghiepVanHoa tTnHoa, ";
                strSQL += " tLopHocSinh tLo, ";
                strSQL += " tHocSinhMon tHo, ";
                strSQL += " mMon mMo, ";
                strSQL += " tHocSinhDiemTotNghiep tChinhTri, ";
                strSQL += " tHocSinhDiemTotNghiep tLyThuyet, ";
                strSQL += " tHocSinhDiemTotNghiep tThucHanh, ";
                strSQL += " tHocSinhTongKet tHoTK ";
                strSQL += " WHERE    tHo3.HS_ID=tVan10.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tVan11.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tVan12.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tToan10.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tToan11.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tToan12.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tLy12.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tLy10.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tLy11.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHoa10.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHoa11.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHoa12.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tTnToan.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tTnLy.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tTnHoa.HS_ID ";
                strSQL += " AND      tLo.HS_ID=tHo3.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHo.HS_ID ";
                strSQL += " AND      mMo.MO_ID=tHo.MO_ID ";
                strSQL += " AND      tHo3.HS_ID=tChinhTri.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tLyThuyet.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tThucHanh.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHoTK.HS_ID ";
                strSQL += " AND      tVan10.MVH_ID = 1 ";
                strSQL += " AND      tVan11.MVH_ID = 1 ";
                strSQL += " AND      tVan12.MVH_ID = 1 ";
                strSQL += " AND      tVan10.CDVH_ID = 2 ";
                strSQL += " AND      tVan11.CDVH_ID = 3 ";
                strSQL += " AND      tVan12.CDVH_ID = 4 ";
                strSQL += " AND      tToan10.MVH_ID = 2 ";
                strSQL += " AND      tToan11.MVH_ID = 2 ";
                strSQL += " AND      tToan12.MVH_ID = 2 ";
                strSQL += " AND      tToan10.CDVH_ID = 2 ";
                strSQL += " AND      tToan11.CDVH_ID = 3 ";
                strSQL += " AND      tToan12.CDVH_ID = 4 ";
                strSQL += " AND      tLy10.MVH_ID = 3 ";
                strSQL += " AND      tLy11.MVH_ID = 3 ";
                strSQL += " AND      tLy12.MVH_ID = 3 ";
                strSQL += " AND      tLy10.CDVH_ID = 2 ";
                strSQL += " AND      tLy11.CDVH_ID = 3 ";
                strSQL += " AND      tLy12.CDVH_ID = 4 ";
                strSQL += " AND      tHoa10.MVH_ID = 4 ";
                strSQL += " AND      tHoa11.MVH_ID = 4 ";
                strSQL += " AND      tHoa12.MVH_ID = 4 ";
                strSQL += " AND      tHoa10.CDVH_ID = 2 ";
                strSQL += " AND      tHoa11.CDVH_ID = 3 ";
                strSQL += " AND      tHoa12.CDVH_ID = 4 ";
                strSQL += " AND      tTnToan.MVH_ID = 2 ";
                strSQL += " AND      tTnLy.MVH_ID = 3 ";
                strSQL += " AND      tTnHoa.MVH_ID = 4 ";
                strSQL += " AND      tChinhTri.Mon_ID = 3 ";
                strSQL += " AND      tLyThuyet.Mon_ID = 1 ";
                strSQL += " AND      tThucHanh.Mon_ID = 2";
                strSQL += " AND      tLo.Lop_ID = " + intLopID;
                strSQL += " ) As tblTemp";
                #endregion

                strSQL += " ) As tblSource";

                strSQL += @" group by HS_ID, hs_hodem, hs_ten, hs_ngaysinh, TongKetVan, TongKetToan, TongKetLy, TongKetHoa, TotNghiepToan
                            ,TotNghiepLy , TotNghiepHoa, DiemChinhTri, DiemLyThuyet, DiemThucHanh, DiemToanKhoa, DiemDanhGiaTotNghiep
                            ,XepLoaiTotNghiep ,DiemRenLuyen ,XepLoaiRenLuyen";
                strSQL += " order by hs_ten, hs_ngaysinh";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// Lấy điểm tổng kết cao đẳng
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="intKdtID"></param>
        /// <param name="LopID"></param>
        /// <returns></returns>
        public static DataTable fncGetTongKetCD(Database objDb, int intKdtID, int intLopID)
        {
            try
            {
                string strSQL = "";

                //lấy số môn cần học trong khung đào tạo.
                List<DTO.MKhungDaoTaoMonDTO> lstKdtMon = DAO.MKhungDaoTaoMonDAO.SelectWhere(objDb, string.Format("KDT_ID = {0}", intKdtID));
                List<DTO.MMonDTO> lstMon = new List<DTO.MMonDTO>();

                for (int i = 0; i <= lstKdtMon.Count - 1; i++)
                {
                    lstMon.Add(DAO.MMonDAO.Select(objDb, lstKdtMon[i].MO_ID));
                }

                //Build caau lệnh query;
                strSQL += " select";
                strSQL += " tblSource.HS_ID";
                strSQL += ",tblSource.hs_hodem as \"Họ đệm\"";
                strSQL += ",tblSource.hs_ten as \"Tên\"";
                strSQL += ",tblSource.hs_ngaysinh as \"Ngày sinh\"";
                
                strSQL += ",tblSource.DiemChinhTri as \"Thi Chính trị\"";
                strSQL += ",tblSource.DiemLyThuyet as \"Thi Lý thuyết\"";
                strSQL += ",tblSource.DiemThucHanh as \"Thi Thực hành\"";

                strSQL += ",tblSource.DiemToanKhoa as \"Điểm TB toàn khóa\"";
                strSQL += ",DiemDanhGiaTotNghiep as \"Điểm đánh giá tốt nghiệp\"";
                strSQL += ",XepLoaiTotNghiep as \"Xếp loại tốt nghiệp\"";
                strSQL += ",DiemRenLuyen as \"Điểm rèn luyện\"";
                strSQL += ",XepLoaiRenLuyen as \"Xếp loại rèn luyện\"";
                for (int i = 0; i <= lstMon.Count - 1; i++)
                {
                    strSQL += ",sum(tblSource.\"" + lstMon[i].Mo_tenmon + "\") as \"" + lstMon[i].Mo_tenmon + "\"";
                }

                strSQL += " from (";
                strSQL += " Select";

                strSQL += " tblTemp.HS_ID, tblTemp.hs_hodem, tblTemp.hs_ten, tblTemp.hs_ngaysinh ";
                strSQL += ",tblTemp.DiemChinhTri, tblTemp.DiemLyThuyet, tblTemp.DiemThucHanh";
                strSQL += ",tblTemp.DiemToanKhoa, tblTemp.DiemDanhGiaTotNghiep, tblTemp.XepLoaiTotNghiep, tblTemp.DiemRenLuyen, tblTemp.XepLoaiRenLuyen ";
                for (int i = 0; i <= lstMon.Count - 1; i++)
                {
                    strSQL += ",case when tblTemp.mo_tenmon=N'" + lstMon[i].Mo_tenmon + "' then tblTemp.HSM_diemtongket else 0 end as \"" + lstMon[i].Mo_tenmon + "\"";
                }

                strSQL += " from";

                #region TempTable
                strSQL += " (";
                strSQL += " SELECT   tHo3.HS_ID, tHo3.hs_hodem, tHo3.hs_ten, tHo3.hs_ngaysinh,";
                strSQL += " tLo.Lop_ID, ";
                strSQL += " tHo.MO_ID, mMo.mo_tenmon, tHo.HSM_diemtongket, ";
                strSQL += " case when tChinhTri.DiemLan1 >= tChinhTri.DiemLan2 then tChinhTri.DiemLan1 else tChinhTri.DiemLan2 end as DiemChinhTri,";
                strSQL += " case when tLyThuyet.DiemLan1 >= tLyThuyet.DiemLan2 then tLyThuyet.DiemLan1 else tLyThuyet.DiemLan2 end as DiemLyThuyet,";
                strSQL += " case when tThucHanh.DiemLan1 >= tThucHanh.DiemLan2 then tThucHanh.DiemLan1 else tThucHanh.DiemLan2 end as DiemThucHanh, ";
                strSQL += " tHoTK.DiemToanKhoa, tHoTK.DiemDanhGiaTotNghiep, ";
                strSQL += " tHoTK.XepLoaiTotNghiep, tHoTK.DiemRenLuyen, ";
                strSQL += " tHoTK.XepLoaiRenLuyen ";
                strSQL += " FROM     tHocSinh tHo3, ";
                strSQL += " tLopHocSinh tLo, ";
                strSQL += " tHocSinhMon tHo, ";
                strSQL += " mMon mMo, ";
                strSQL += " tHocSinhDiemTotNghiep tChinhTri, ";
                strSQL += " tHocSinhDiemTotNghiep tLyThuyet, ";
                strSQL += " tHocSinhDiemTotNghiep tThucHanh, ";
                strSQL += " tHocSinhTongKet tHoTK ";
                strSQL += " WHERE    tLo.HS_ID=tHo3.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHo.HS_ID ";
                strSQL += " AND      mMo.MO_ID=tHo.MO_ID ";
                strSQL += " AND      tHo3.HS_ID=tChinhTri.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tLyThuyet.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tThucHanh.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHoTK.HS_ID ";
                strSQL += " AND      tChinhTri.Mon_ID = 3 ";
                strSQL += " AND      tLyThuyet.Mon_ID = 1 ";
                strSQL += " AND      tThucHanh.Mon_ID = 2";
                strSQL += " AND      tLo.Lop_ID = " + intLopID;
                strSQL += " ) As tblTemp";
                #endregion

                strSQL += " ) As tblSource";

                strSQL += @" group by HS_ID, hs_hodem, hs_ten, hs_ngaysinh, DiemChinhTri, DiemLyThuyet, DiemThucHanh, DiemToanKhoa, DiemDanhGiaTotNghiep
                            ,XepLoaiTotNghiep ,DiemRenLuyen ,XepLoaiRenLuyen";
                strSQL += " order by hs_ten, hs_ngaysinh";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// Check if username is exist
        /// </summary>
        /// <param name="strUsername"></param>
        /// <returns></returns>
        public static bool fncIsUserExist(string strUsername)
        {
            try
            {
                //truy vấn xem user có tồn tại hay không
                DataTable objData = DAO.TblUserDAO.SelectWhere(Program.gobjDb, string.Format("Username = '{0}'", strUsername), true);

                //trả về false nếu không tồn tại
                if (objData == null) return false;
                if (objData.Rows.Count <= 0) return false;

                return true;
            }
            catch { return true; }
        }

        #region Phục vụ phân cụm

        /// <summary>
        /// Lấy điểm tổng kết sơ cấp
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="intKdtID"></param>
        /// <param name="LopID"></param>
        /// <returns></returns>
        public static DataTable fncGetTongKetSC2(Database objDb, int intKdtID, int intLopID)
        {
            try
            {
                string strSQL = "";

                //lấy số môn cần học trong khung đào tạo.
                List<DTO.MKhungDaoTaoMonDTO> lstKdtMon = DAO.MKhungDaoTaoMonDAO.SelectWhere(objDb, string.Format("KDT_ID = {0}", intKdtID));
                List<DTO.MMonDTO> lstMon = new List<DTO.MMonDTO>();

                for (int i = 0; i <= lstKdtMon.Count - 1; i++)
                {
                    lstMon.Add(DAO.MMonDAO.Select(objDb, lstKdtMon[i].MO_ID));
                }

                //Build caau lệnh query;
                strSQL += " select";
                //strSQL += " tblSource.Lop_ID";
                //strSQL += ",tblSource.Lop_ten";
                strSQL += " tblSource.HS_ID";
                strSQL += ",tblSource.hs_hodem as \"Họ đệm\"";
                strSQL += ",tblSource.hs_ten as \"Tên\"";
                strSQL += ",tblSource.hs_ngaysinh as \"Ngày sinh\"";
                //strSQL += ",tblSource.Mon_ID";
                strSQL += ",tblSource.DiemKTKT as \"Điểm KTKT\"";
                strSQL += ",DiemDanhGiaTotNghiep as \"Điểm đánh giá tốt nghiệp\"";
                strSQL += ",XepLoaiTotNghiep as \"Xếp loại tốt nghiệp\"";
                strSQL += ",DiemRenLuyen as \"Điểm rèn luyện\"";
                strSQL += ",XepLoaiRenLuyen as \"Xếp loại rèn luyện\"";
                for (int i = 0; i <= lstMon.Count - 1; i++)
                {
                    //strSQL += ",Format(sum(tblSource.\"" + lstMon[i].Mo_tenmon + "\"), 9.99) as \"" + lstMon[i].Mo_tenmon + "\"";
                    strSQL += ",sum(tblSource.\"" + lstMon[i].Mo_tenmon + "\") as \"" + lstMon[i].Mo_tenmon + "\"";
                }

                strSQL += " from (";

                strSQL += " select tblTemp.Lop_ID, tblTemp.Lop_ten, tblTemp.HS_ID, tblTemp.hs_hodem, tblTemp.hs_ten, tblTemp.hs_ngaysinh, tblTemp.Mon_ID, tblTemp.DiemKTKT,";
                strSQL += " DiemDanhGiaTotNghiep, XepLoaiTotNghiep, DiemRenLuyen, XepLoaiRenLuyen";
                for (int i = 0; i <= lstMon.Count - 1; i++)
                {
                    strSQL += ",case when tblTemp.mo_tenmon=N'" + lstMon[i].Mo_tenmon + "' then tblTemp.HSM_diemtongket else 0 end as \"" + lstMon[i].Mo_tenmon + "\"";
                }

                strSQL += " from";

                strSQL += " (";
                strSQL += " SELECT   mLo.Lop_ID, mLo.Lop_ten, tLo.HS_ID, tHo.hs_hodem, tHo.hs_ten,";
                strSQL += " tHo.hs_ngaysinh, tHo3.Mon_ID,";
                strSQL += " case when tHo3.DiemLan1 > tHo3.DiemLan2 then  tHo3.DiemLan1 else tHo3.DiemLan2 end   AS DiemKTKT,";
                strSQL += " mMo.mo_tenmon, tHo1.HSM_diemtongket, mKh.HeSoMon,";
                strSQL += " mKh.TinhDiemTongKet, tHo2.DiemToanKhoa,";
                strSQL += " tHo2.DiemDanhGiaTotNghiep, tHo2.XepLoaiTotNghiep,";
                strSQL += " tHo2.DiemRenLuyen, tHo2.XepLoaiRenLuyen";
                strSQL += " FROM     mKhungDaoTaoMon mKh,";
                strSQL += " mLop mLo,";
                strSQL += " tLopHocSinh tLo,";
                strSQL += " tHocSinh tHo,";
                strSQL += " tHocSinhMon tHo1,";
                strSQL += " tHocSinhTongKet tHo2,";
                strSQL += " mMon mMo,";
                strSQL += " tHocSinhDiemTotNghiep tHo3";
                strSQL += " WHERE    mKh.KDT_ID=mLo.KDT_ID";
                strSQL += " AND      mLo.Lop_ID=tLo.Lop_ID";
                strSQL += " AND      tLo.HS_ID=tHo.HS_ID";
                strSQL += " AND      tHo.HS_ID=tHo1.HS_ID";
                strSQL += " AND      tHo.HS_ID=tHo2.HS_ID";
                strSQL += " AND      tHo1.MO_ID=mMo.MO_ID";
                strSQL += " AND      tHo.HS_ID=tHo3.HS_ID";
                strSQL += " AND      mKh.MO_ID=tHo1.MO_ID";
                strSQL += " AND      mLo.Lop_ID = " + intLopID;
                strSQL += " AND      tHo3.Mon_ID = 4";
                strSQL += " ) As tblTemp";

                strSQL += " ) As tblSource";

                strSQL += " group by Lop_ID, Lop_ten, HS_ID, hs_hodem, hs_ten, hs_ngaysinh, Mon_ID, DiemKTKT, DiemDanhGiaTotNghiep, XepLoaiTotNghiep, DiemRenLuyen, XepLoaiRenLuyen";
                strSQL += " order by Lop_ID, Lop_ten, HS_ID, hs_hodem, hs_ten, hs_ngaysinh, Mon_ID, DiemKTKT";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// Lấy điểm tổng kết trung cấp
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="intKdtID"></param>
        /// <param name="LopID"></param>
        /// <returns></returns>
        public static DataTable fncGetTongKetTC2(Database objDb, int intKdtID, int intLopID)
        {
            try
            {
                string strSQL = "";

                //lấy số môn cần học trong khung đào tạo.
                List<DTO.MKhungDaoTaoMonDTO> lstKdtMon = DAO.MKhungDaoTaoMonDAO.SelectWhere(objDb, string.Format("KDT_ID = {0}", intKdtID));
                List<DTO.MMonDTO> lstMon = new List<DTO.MMonDTO>();

                for (int i = 0; i <= lstKdtMon.Count - 1; i++)
                {
                    lstMon.Add(DAO.MMonDAO.Select(objDb, lstKdtMon[i].MO_ID));
                }

                //Build caau lệnh query;
                strSQL += " select";
                //strSQL += " tblSource.Lop_ID";
                //strSQL += ",tblSource.Lop_ten";
                strSQL += " tblSource.HS_ID";
                strSQL += ",tblSource.hs_hodem as \"Họ đệm\"";
                strSQL += ",tblSource.hs_ten as \"Tên\"";
                strSQL += ",tblSource.hs_ngaysinh as \"Ngày sinh\"";

                strSQL += ",tblSource.TongKetVan as \"Tổng kết Văn\"";
                strSQL += ",tblSource.TongKetToan as \"Tổng kết Toán\"";
                strSQL += ",tblSource.TongKetLy as \"Tổng kết Lý\"";
                strSQL += ",tblSource.TongKetHoa as \"Tổng kết Hóa\"";

                strSQL += ",tblSource.TotNghiepToan as \"Thi TN Toán\"";
                strSQL += ",tblSource.TotNghiepLy as \"Thi TN Lý\"";
                strSQL += ",tblSource.TotNghiepHoa as \"Thi TN Hóa\"";

                strSQL += ",tblSource.DiemChinhTri as \"Thi Chính trị\"";
                strSQL += ",tblSource.DiemLyThuyet as \"Thi Lý thuyết\"";
                strSQL += ",tblSource.DiemThucHanh as \"Thi Thực hành\"";

                strSQL += ",tblSource.DiemToanKhoa as \"Điểm TB toàn khóa\"";
                strSQL += ",DiemDanhGiaTotNghiep as \"Điểm đánh giá tốt nghiệp\"";
                strSQL += ",XepLoaiTotNghiep as \"Xếp loại tốt nghiệp\"";
                strSQL += ",DiemRenLuyen as \"Điểm rèn luyện\"";
                strSQL += ",XepLoaiRenLuyen as \"Xếp loại rèn luyện\"";
                
                //thêm cho phân cụm 1
                strSQL += " ,hs_khaosattruockhihoc, hs_coviecsautotnghiep, hs_uutien, DD_ID, TheHocNghe,tdvh_ten ";

                for (int i = 0; i <= lstMon.Count - 1; i++)
                {
                    //strSQL += ",Format(sum(tblSource.\"" + lstMon[i].Mo_tenmon + "\"), 9.99) as \"" + lstMon[i].Mo_tenmon + "\"";
                    strSQL += ",sum(tblSource.\"" + lstMon[i].Mo_tenmon + "\") as \"" + lstMon[i].Mo_tenmon + "\"";
                }

                strSQL += " from (";
                strSQL += " Select";

                strSQL += " tblTemp.HS_ID, tblTemp.hs_hodem, tblTemp.hs_ten, tblTemp.hs_ngaysinh ";
                strSQL += ",tblTemp.TongKetVan, tblTemp.TongKetToan ,tblTemp.TongKetLy, tblTemp.TongKetHoa";
                strSQL += ",tblTemp.TotNghiepToan, tblTemp.TotNghiepLy, tblTemp.TotNghiepHoa";
                strSQL += ",tblTemp.DiemChinhTri, tblTemp.DiemLyThuyet, tblTemp.DiemThucHanh";
                strSQL += ",tblTemp.DiemToanKhoa, tblTemp.DiemDanhGiaTotNghiep, tblTemp.XepLoaiTotNghiep, tblTemp.DiemRenLuyen, tblTemp.XepLoaiRenLuyen ";
                for (int i = 0; i <= lstMon.Count - 1; i++)
                {
                    strSQL += ",case when tblTemp.mo_tenmon=N'" + lstMon[i].Mo_tenmon + "' then tblTemp.HSM_diemtongket else 0 end as \"" + lstMon[i].Mo_tenmon + "\"";
                }

                //thêm cho phân cụm 2
                strSQL += " ,tblTemp.hs_khaosattruockhihoc, tblTemp.hs_coviecsautotnghiep, tblTemp.hs_uutien,tblTemp.DD_ID, tblTemp.TheHocNghe, tblTemp.tdvh_ten";

                strSQL += " from";

                #region TempTable
                strSQL += " (";
                strSQL += " SELECT   tHo3.HS_ID, tHo3.hs_hodem, tHo3.hs_ten, tHo3.hs_ngaysinh, ";

                //thêm cho phân cụm 3
                strSQL += " tHo3.hs_khaosattruockhihoc, tHo3.hs_coviecsautotnghiep, tHo3.hs_uutien, tHo3.DD_ID, tDt.TheHocNghe, mTdvh.tdvh_ten, ";

                strSQL += " (tVan10.diemtongket + tVan11.diemtongket + tVan12.diemtongket)/3  AS TongKetVan, ";
                strSQL += " (tToan10.diemtongket + tToan11.diemtongket + tToan12.diemtongket)/3  AS TongKetToan, ";
                strSQL += " (tLy10.diemtongket + tLy11.diemtongket + tLy12.diemtongket)/3  AS TongKetLy, ";
                strSQL += " (tHoa10.diemtongket + tHoa11.diemtongket + tHoa12.diemtongket)/3  AS TongKetHoa, ";
                strSQL += " tTnToan.tnvh_diemtotnghiep AS TotNghiepToan, ";
                strSQL += " tTnLy.tnvh_diemtotnghiep AS TotNghiepLy, ";
                strSQL += " tTnHoa.tnvh_diemtotnghiep AS TotNghiepHoa, tLo.Lop_ID, ";
                strSQL += " tHo.MO_ID, mMo.mo_tenmon, tHo.HSM_diemtongket, ";
                strSQL += " case when tChinhTri.DiemLan1 >= tChinhTri.DiemLan2 then tChinhTri.DiemLan1 else tChinhTri.DiemLan2 end as DiemChinhTri,";
                strSQL += " case when tLyThuyet.DiemLan1 >= tLyThuyet.DiemLan2 then tLyThuyet.DiemLan1 else tLyThuyet.DiemLan2 end as DiemLyThuyet,";
                strSQL += " case when tThucHanh.DiemLan1 >= tThucHanh.DiemLan2 then tThucHanh.DiemLan1 else tThucHanh.DiemLan2 end as DiemThucHanh, ";
                strSQL += " tHoTK.DiemToanKhoa, tHoTK.DiemDanhGiaTotNghiep, ";
                strSQL += " tHoTK.XepLoaiTotNghiep, tHoTK.DiemRenLuyen, ";
                strSQL += " tHoTK.XepLoaiRenLuyen ";
                strSQL += " FROM     tHocSinh tHo3, ";
                strSQL += " tHocSinhMonVanHoa tVan10, ";
                strSQL += " tHocSinhMonVanHoa tVan11, ";
                strSQL += " tHocSinhMonVanHoa tVan12, ";
                strSQL += " tHocSinhMonVanHoa tToan10, ";
                strSQL += " tHocSinhMonVanHoa tToan11, ";
                strSQL += " tHocSinhMonVanHoa tToan12, ";
                strSQL += " tHocSinhMonVanHoa tLy12, ";
                strSQL += " tHocSinhMonVanHoa tLy10, ";
                strSQL += " tHocSinhMonVanHoa tLy11, ";
                strSQL += " tHocSinhMonVanHoa tHoa10, ";
                strSQL += " tHocSinhMonVanHoa tHoa11, ";
                strSQL += " tHocSinhMonVanHoa tHoa12, ";
                strSQL += " tHocSinhDiemTotNghiepVanHoa tTnToan, ";
                strSQL += " tHocSinhDiemTotNghiepVanHoa tTnLy, ";
                strSQL += " tHocSinhDiemTotNghiepVanHoa tTnHoa, ";
                strSQL += " tLopHocSinh tLo, ";
                strSQL += " tHocSinhMon tHo, ";
                strSQL += " mMon mMo, ";
                strSQL += " tHocSinhDiemTotNghiep tChinhTri, ";
                strSQL += " tHocSinhDiemTotNghiep tLyThuyet, ";
                strSQL += " tHocSinhDiemTotNghiep tThucHanh, ";
                strSQL += " tHocSinhTongKet tHoTK ";

                //thêm cho phân cụm 4
                strSQL += ",tDanhSachDuTuyen tDt ";
                strSQL += ",mTrinhDoVanHoa mTdvh ";

                strSQL += " WHERE    tHo3.HS_ID=tVan10.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tVan11.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tVan12.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tToan10.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tToan11.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tToan12.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tLy12.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tLy10.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tLy11.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHoa10.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHoa11.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHoa12.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tTnToan.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tTnLy.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tTnHoa.HS_ID ";
                strSQL += " AND      tLo.HS_ID=tHo3.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHo.HS_ID ";
                strSQL += " AND      mMo.MO_ID=tHo.MO_ID ";
                strSQL += " AND      tHo3.HS_ID=tChinhTri.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tLyThuyet.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tThucHanh.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHoTK.HS_ID ";
                strSQL += " AND      tVan10.MVH_ID = 1 ";
                strSQL += " AND      tVan11.MVH_ID = 1 ";
                strSQL += " AND      tVan12.MVH_ID = 1 ";
                strSQL += " AND      tVan10.CDVH_ID = 2 ";
                strSQL += " AND      tVan11.CDVH_ID = 3 ";
                strSQL += " AND      tVan12.CDVH_ID = 4 ";
                strSQL += " AND      tToan10.MVH_ID = 2 ";
                strSQL += " AND      tToan11.MVH_ID = 2 ";
                strSQL += " AND      tToan12.MVH_ID = 2 ";
                strSQL += " AND      tToan10.CDVH_ID = 2 ";
                strSQL += " AND      tToan11.CDVH_ID = 3 ";
                strSQL += " AND      tToan12.CDVH_ID = 4 ";
                strSQL += " AND      tLy10.MVH_ID = 3 ";
                strSQL += " AND      tLy11.MVH_ID = 3 ";
                strSQL += " AND      tLy12.MVH_ID = 3 ";
                strSQL += " AND      tLy10.CDVH_ID = 2 ";
                strSQL += " AND      tLy11.CDVH_ID = 3 ";
                strSQL += " AND      tLy12.CDVH_ID = 4 ";
                strSQL += " AND      tHoa10.MVH_ID = 4 ";
                strSQL += " AND      tHoa11.MVH_ID = 4 ";
                strSQL += " AND      tHoa12.MVH_ID = 4 ";
                strSQL += " AND      tHoa10.CDVH_ID = 2 ";
                strSQL += " AND      tHoa11.CDVH_ID = 3 ";
                strSQL += " AND      tHoa12.CDVH_ID = 4 ";
                strSQL += " AND      tTnToan.MVH_ID = 2 ";
                strSQL += " AND      tTnLy.MVH_ID = 3 ";
                strSQL += " AND      tTnHoa.MVH_ID = 4 ";
                strSQL += " AND      tChinhTri.Mon_ID = 3 ";
                strSQL += " AND      tLyThuyet.Mon_ID = 1 ";
                strSQL += " AND      tThucHanh.Mon_ID = 2";
                strSQL += " AND      tLo.Lop_ID = " + intLopID;

                //thêm cho phân cụm 5
                strSQL += " AND      tDt.HS_ID=tHo3.HS_ID ";
                strSQL += " AND      mTdvh.TDVH_ID=tHo3.TDVH_ID ";

                strSQL += " ) As tblTemp";
                #endregion

                strSQL += " ) As tblSource";

                strSQL += @" group by HS_ID, hs_hodem, hs_ten, hs_ngaysinh, TongKetVan, TongKetToan, TongKetLy, TongKetHoa, TotNghiepToan
                            ,TotNghiepLy , TotNghiepHoa, DiemChinhTri, DiemLyThuyet, DiemThucHanh, DiemToanKhoa, DiemDanhGiaTotNghiep
                            ,XepLoaiTotNghiep ,DiemRenLuyen ,XepLoaiRenLuyen";

                //thêm cho phân cụm 6
                strSQL += " ,hs_khaosattruockhihoc, hs_coviecsautotnghiep, hs_uutien,DD_ID, TheHocNghe,tdvh_ten";

                strSQL += " order by hs_ten, hs_ngaysinh";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// Lấy điểm tổng kết cao đẳng
        /// </summary>
        /// <param name="objDb"></param>
        /// <param name="intKdtID"></param>
        /// <param name="LopID"></param>
        /// <returns></returns>
        public static DataTable fncGetTongKetCD2(Database objDb, int intKdtID, int intLopID)
        {
            try
            {
                string strSQL = "";

                //lấy số môn cần học trong khung đào tạo.
                List<DTO.MKhungDaoTaoMonDTO> lstKdtMon = DAO.MKhungDaoTaoMonDAO.SelectWhere(objDb, string.Format("KDT_ID = {0}", intKdtID));
                List<DTO.MMonDTO> lstMon = new List<DTO.MMonDTO>();

                for (int i = 0; i <= lstKdtMon.Count - 1; i++)
                {
                    lstMon.Add(DAO.MMonDAO.Select(objDb, lstKdtMon[i].MO_ID));
                }

                //Build caau lệnh query;
                strSQL += " select";
                strSQL += " tblSource.HS_ID";
                strSQL += ",tblSource.hs_hodem as \"Họ đệm\"";
                strSQL += ",tblSource.hs_ten as \"Tên\"";
                strSQL += ",tblSource.hs_ngaysinh as \"Ngày sinh\"";

                strSQL += ",tblSource.DiemChinhTri as \"Thi Chính trị\"";
                strSQL += ",tblSource.DiemLyThuyet as \"Thi Lý thuyết\"";
                strSQL += ",tblSource.DiemThucHanh as \"Thi Thực hành\"";

                strSQL += ",tblSource.DiemToanKhoa as \"Điểm TB toàn khóa\"";
                strSQL += ",DiemDanhGiaTotNghiep as \"Điểm đánh giá tốt nghiệp\"";
                strSQL += ",XepLoaiTotNghiep as \"Xếp loại tốt nghiệp\"";
                strSQL += ",DiemRenLuyen as \"Điểm rèn luyện\"";
                strSQL += ",XepLoaiRenLuyen as \"Xếp loại rèn luyện\"";
                for (int i = 0; i <= lstMon.Count - 1; i++)
                {
                    strSQL += ",sum(tblSource.\"" + lstMon[i].Mo_tenmon + "\") as \"" + lstMon[i].Mo_tenmon + "\"";
                }

                strSQL += " from (";
                strSQL += " Select";

                strSQL += " tblTemp.HS_ID, tblTemp.hs_hodem, tblTemp.hs_ten, tblTemp.hs_ngaysinh ";
                strSQL += ",tblTemp.DiemChinhTri, tblTemp.DiemLyThuyet, tblTemp.DiemThucHanh";
                strSQL += ",tblTemp.DiemToanKhoa, tblTemp.DiemDanhGiaTotNghiep, tblTemp.XepLoaiTotNghiep, tblTemp.DiemRenLuyen, tblTemp.XepLoaiRenLuyen ";
                for (int i = 0; i <= lstMon.Count - 1; i++)
                {
                    strSQL += ",case when tblTemp.mo_tenmon=N'" + lstMon[i].Mo_tenmon + "' then tblTemp.HSM_diemtongket else 0 end as \"" + lstMon[i].Mo_tenmon + "\"";
                }

                strSQL += " from";

                #region TempTable
                strSQL += " (";
                strSQL += " SELECT   tHo3.HS_ID, tHo3.hs_hodem, tHo3.hs_ten, tHo3.hs_ngaysinh,";
                strSQL += " tLo.Lop_ID, ";
                strSQL += " tHo.MO_ID, mMo.mo_tenmon, tHo.HSM_diemtongket, ";
                strSQL += " case when tChinhTri.DiemLan1 >= tChinhTri.DiemLan2 then tChinhTri.DiemLan1 else tChinhTri.DiemLan2 end as DiemChinhTri,";
                strSQL += " case when tLyThuyet.DiemLan1 >= tLyThuyet.DiemLan2 then tLyThuyet.DiemLan1 else tLyThuyet.DiemLan2 end as DiemLyThuyet,";
                strSQL += " case when tThucHanh.DiemLan1 >= tThucHanh.DiemLan2 then tThucHanh.DiemLan1 else tThucHanh.DiemLan2 end as DiemThucHanh, ";
                strSQL += " tHoTK.DiemToanKhoa, tHoTK.DiemDanhGiaTotNghiep, ";
                strSQL += " tHoTK.XepLoaiTotNghiep, tHoTK.DiemRenLuyen, ";
                strSQL += " tHoTK.XepLoaiRenLuyen ";
                strSQL += " FROM     tHocSinh tHo3, ";
                strSQL += " tLopHocSinh tLo, ";
                strSQL += " tHocSinhMon tHo, ";
                strSQL += " mMon mMo, ";
                strSQL += " tHocSinhDiemTotNghiep tChinhTri, ";
                strSQL += " tHocSinhDiemTotNghiep tLyThuyet, ";
                strSQL += " tHocSinhDiemTotNghiep tThucHanh, ";
                strSQL += " tHocSinhTongKet tHoTK ";
                strSQL += " WHERE    tLo.HS_ID=tHo3.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHo.HS_ID ";
                strSQL += " AND      mMo.MO_ID=tHo.MO_ID ";
                strSQL += " AND      tHo3.HS_ID=tChinhTri.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tLyThuyet.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tThucHanh.HS_ID ";
                strSQL += " AND      tHo3.HS_ID=tHoTK.HS_ID ";
                strSQL += " AND      tChinhTri.Mon_ID = 3 ";
                strSQL += " AND      tLyThuyet.Mon_ID = 1 ";
                strSQL += " AND      tThucHanh.Mon_ID = 2";
                strSQL += " AND      tLo.Lop_ID = " + intLopID;
                strSQL += " ) As tblTemp";
                #endregion

                strSQL += " ) As tblSource";

                strSQL += @" group by HS_ID, hs_hodem, hs_ten, hs_ngaysinh, DiemChinhTri, DiemLyThuyet, DiemThucHanh, DiemToanKhoa, DiemDanhGiaTotNghiep
                            ,XepLoaiTotNghiep ,DiemRenLuyen ,XepLoaiRenLuyen";
                strSQL += " order by hs_ten, hs_ngaysinh";

                System.Data.Common.DbCommand objCmnd = objDb.GetSqlStringCommand(strSQL);
                using (IDataReader dataReader = objDb.ExecuteReader(objCmnd))
                {
                    if (dataReader.FieldCount > 0)
                    {
                        DataTable dt = new DataTable();
                        dt.Load(dataReader);
                        if (dt.Rows.Count <= 0) return null;
                        return dt;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch { return null; }
        }

        #endregion
    }
}